﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlServerCe;
using System.Windows;
using System.Data;
using System.Windows.Forms;
using System.Collections.ObjectModel;
using System.IO;

namespace Organizer
{
    class DataBase
    {
        public static bool isName = false;

        public static string DataBaseName = "Database.sdf";
        public static string pathFolder = @"c:/Users/" + Environment.UserName + "/.Organizer/";
        public static string connStr = "Data Source=" + pathFolder + DataBaseName;
        public static string errorDB = "Something wrong :(";

        public static bool checkDB()
        {
            if (File.Exists(pathFolder+DataBaseName))
                return true;
            else
                return false;
        }

        public static void creatBD()
        {
            Directory.CreateDirectory(pathFolder);

            SqlCeEngine engine = new SqlCeEngine(connStr);
            engine.CreateDatabase();

            SqlCeConnection conn = null;
            try
            {
                conn = new SqlCeConnection(connStr);
                conn.Open();

                SqlCeCommand cmd = conn.CreateCommand();
                cmd.CommandText = "CREATE TABLE [Tasks] ( [ID] int IDENTITY (1,1) NOT NULL PRIMARY KEY, [NAME] nvarchar(100) NULL, "
                                + "[DEFINITION] nvarchar(100) NULL, [STARTDATE] datetime NULL, [ENDDATE] datetime NULL ,"
                                + "[HOURS] int NULL, [MINS] int NULL, [SPENDHOURS] int NULL, [SPENDMINS] int NULL, [SPENDSECS] int NULL )";
                cmd.ExecuteNonQuery();

                cmd.CommandText = "CREATE TABLE [Stats] ( [ID] int NOT NULL PRIMARY KEY, [ALLTASKS] int NULL, "
                                + "[DONETASKS] int NULL, [PROCESSESTASKS] int NULL, [EVALUATETIME] int NULL ,"
                                + "[SPENDTIME] int  NULL, [ERROREVALUATE] int NULL, [ERROREVALUATEBYTASK] int NULL)";
                cmd.ExecuteNonQuery();
            }
            catch
            {
                System.Windows.Forms.MessageBox.Show(errorDB);
            }
            finally
            {
                conn.Close();
                conn.Dispose();
            }

        }

        public static int getMaxId()
        {
            int temp = 0;
            SqlCeConnection conn = new SqlCeConnection(connStr);
            try
            {
                conn.Open();
            }
            catch
            {
                System.Windows.Forms.MessageBox.Show(errorDB);
                return 0;
            }

            SqlCeCommand cmd = new SqlCeCommand("Select MAX(ID) From Tasks", conn);
            using (SqlCeDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
            {
                if (dr.Read())
                {
                    if (int.TryParse(dr.GetValue(0).ToString(), out temp))
                        temp++;
                    else temp = 0;
                }
            }
            
            conn.Close();
            conn.Dispose();

            return temp;
        }

        public static void InsDB(Task task)
        {
            SqlCeConnection conn = new SqlCeConnection(connStr);
            try
            {
                conn.Open();
            }
            catch
            {
                System.Windows.Forms.MessageBox.Show(errorDB);
                return;
            }
            SqlCeCommand cmd = new SqlCeCommand("Insert into Tasks" +
               "(NAME,DEFINITION,STARTDATE,ENDDATE,HOURS,MINS,SPENDHOURS,SPENDMINS,SPENDSECS) Values (@NAME,@DEFINITION,@STARTDATE,@ENDDATE,@HOURS,@MINS,@Sh,@Sm,@Ss)", conn);
            SqlCeParameter param = new SqlCeParameter();

            cmd.Parameters.AddWithValue("@Name", task.name);
            cmd.Parameters.AddWithValue("@DEFINITION", task.definition);
            cmd.Parameters.AddWithValue("@STARTDATE", task.startDate);
            cmd.Parameters.AddWithValue("@ENDDATE", task.endDate);
            cmd.Parameters.AddWithValue("@HOURS", task.hours);
            cmd.Parameters.AddWithValue("@MINS", task.mins);
            cmd.Parameters.AddWithValue("@Sh", task.spendHour);
            cmd.Parameters.AddWithValue("@Sm", task.spendMin);
            cmd.Parameters.AddWithValue("@Ss", task.spendSec);

            try
            {
                cmd.ExecuteNonQuery();
            }
            catch
            {
                System.Windows.Forms.MessageBox.Show(errorDB);
                return;
            }
            conn.Close();
            conn.Dispose();
            return;
        }

        public static ObservableCollection<Task> Gets()
        {
            ObservableCollection<Task> temp = new ObservableCollection<Task>();

            SqlCeConnection conn = new SqlCeConnection(connStr);
            try
            {
                conn.Open();
            }
            catch 
            {
                System.Windows.Forms.MessageBox.Show(errorDB);
                return null;
            } 

            SqlCeCommand cmd = new SqlCeCommand("Select * From Tasks", conn);
            using (SqlCeDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
            {
                while (dr.Read())
                {
                    Task task = new Task();
                    //task.id = Convert.ToInt32(dr.GetValue(0).ToString());
                    task.id = Convert.ToInt32(dr["ID"].ToString());
                    task.name = dr.GetValue(1).ToString();
                    task.definition = dr.GetValue(2).ToString();
                    string td = dr.GetValue(3).ToString();
                    task.startDate = Convert.ToDateTime(td);
                    String tp = dr.GetValue(4).ToString();
                    task.endDate = Convert.ToDateTime(tp);
                    task.hours =Convert.ToInt32(dr.GetValue(5).ToString());
                    task.mins = Convert.ToInt32(dr.GetValue(6).ToString());
                    task.spendHour = Convert.ToInt32(dr.GetValue(7).ToString());
                    task.spendMin = Convert.ToInt32(dr.GetValue(8).ToString());
                    task.spendSec = Convert.ToInt32(dr.GetValue(9).ToString());

                    temp.Add(task);
                }
            }
 
            conn.Close();
            conn.Dispose();

            return temp;
        }

        public static void Update(Task task)
        {
            SqlCeConnection conn = new SqlCeConnection(connStr);
            try
            {
                conn.Open();
            }
            catch
            {
                System.Windows.Forms.MessageBox.Show(errorDB);
                return;
            }

            SqlCeCommand cmd = new SqlCeCommand("UPDATE Tasks SET NAME=@name, DEFINITION=@definition, STARTDATE=@startdate, ENDDATE=@enddate,HOURS=@hours,MINS=@mins  WHERE ID=@id", conn);
           // SqlCeParameter param = new SqlCeParameter();

            cmd.Parameters.AddWithValue("@id", task.id);
            cmd.Parameters.AddWithValue("@name", task.name);
            cmd.Parameters.AddWithValue("@definition", task.definition);
            cmd.Parameters.AddWithValue("@startdate", task.startDate);
            cmd.Parameters.AddWithValue("@enddate", task.endDate);
            cmd.Parameters.AddWithValue("@hours", task.hours);
            cmd.Parameters.AddWithValue("@mins", task.mins);

            try
            {
                cmd.ExecuteNonQuery();
            }
            catch
            {
                System.Windows.Forms.MessageBox.Show(errorDB);
                return;
            }

            conn.Close();
            conn.Dispose();
        }

        public static void Delete(Task task)
        {
            SqlCeConnection conn = new SqlCeConnection(connStr);
            try
            {
                conn.Open();
            }
            catch
            {
                System.Windows.Forms.MessageBox.Show(errorDB);
                return;
            }

            SqlCeCommand cmd = new SqlCeCommand("DELETE FROM Tasks WHERE ID=@id", conn);
            SqlCeParameter param = new SqlCeParameter();

            cmd.Parameters.AddWithValue("@id", task.id);

            try
            {
                cmd.ExecuteNonQuery();
            }
            catch
            {
                System.Windows.Forms.MessageBox.Show(errorDB);
                return;
            }

            conn.Close();
            conn.Dispose();

        }

        public static void inTime(Task task)
        {
            SqlCeConnection conn = new SqlCeConnection(connStr);
            try
            {
                conn.Open();
            }
            catch
            {
                System.Windows.Forms.MessageBox.Show(errorDB);
                return;
            }

            SqlCeCommand cmd = new SqlCeCommand("UPDATE Tasks SET SPENDHOURS=@hours, SPENDMINS=@mins, SPENDSECS=@secs  WHERE ID=@id", conn);
            // SqlCeParameter param = new SqlCeParameter();

            cmd.Parameters.AddWithValue("@id", task.id);
            cmd.Parameters.AddWithValue("@hours", task.spendHour);
            cmd.Parameters.AddWithValue("@mins", task.spendMin);
            cmd.Parameters.AddWithValue("@secs", task.spendSec);
            try
            {
                cmd.ExecuteNonQuery();
            }
            catch
            {
                System.Windows.Forms.MessageBox.Show(errorDB);
                return;
            }

            conn.Close();
            conn.Dispose();
        }


        public static Statistic GetStats()
        {
            Statistic st = new Statistic();
            SqlCeConnection conn = new SqlCeConnection(connStr);
            try
            {
                conn.Open();
            }
            catch
            {
                System.Windows.Forms.MessageBox.Show(errorDB);
                return null;
            }

            SqlCeCommand cmd = new SqlCeCommand("Select * From Stats WHERE ID=1", conn);
            using (SqlCeDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
            {
                while (dr.Read())
                {
                    
                    //task.id = Convert.ToInt32(dr.GetValue(0).ToString());
                    // = Convert.ToInt32(dr["ID"].ToString());
                    st.allTasks = Convert.ToInt32(dr.GetValue(1));
                    st.doneTasks = Convert.ToInt32(dr.GetValue(2));
                    st.processesTasks = Convert.ToInt32(dr.GetValue(3));

                    int tp = Convert.ToInt32(dr.GetValue(4));
                    st.evaluateTime = new TimeSpan(tp/3600, (tp/60)%60, tp%60);

                    tp = Convert.ToInt32(dr.GetValue(5));
                    st.spendTime = new TimeSpan(tp / 3600, (tp / 60) % 60, tp % 60);

                    tp = Convert.ToInt32(dr.GetValue(6));
                    st.errorEvaluate = new TimeSpan(tp / 3600, (tp / 60) % 60, tp % 60);

                    tp = Convert.ToInt32(dr.GetValue(5));
                    st.errorEvaluateByTask = new TimeSpan(tp/3600, (tp/60)%60, tp%60);

                }
            }

            conn.Close();
            conn.Dispose();

            return st;
        }

        public static void StatsAdd(Statistic st)
        {
            SqlCeConnection conn = new SqlCeConnection(connStr);
            try
            {
                conn.Open();
            }
            catch
            {
                System.Windows.Forms.MessageBox.Show(errorDB);
                return;
            }

            SqlCeCommand cmd;
            //zapilivaem ishodnie dannie

            cmd = new SqlCeCommand("UPDATE Stats SET ALLTASKS=@alltasks, PROCESSESTASKS=@processestasks, EVALUATETIME=@evaluatetime WHERE ID=1", conn);
            cmd.Parameters.AddWithValue("@alltasks", st.allTasks);         
            cmd.Parameters.AddWithValue("@processestasks", st.processesTasks); 
            cmd.Parameters.AddWithValue("@evaluatetime",st.evaluateTime.Hours*60*60+st.evaluateTime.Minutes*60+st.evaluateTime.Seconds); 

            try
            {
                  cmd.ExecuteNonQuery();
            }
            catch
            {
                System.Windows.Forms.MessageBox.Show(errorDB);
                return;
            }

            conn.Close();
            conn.Dispose();

        }

        public static void StatsPause(Statistic st)
        {
            SqlCeConnection conn = new SqlCeConnection(connStr);
            try
            {
                conn.Open();
            }
            catch
            {
                System.Windows.Forms.MessageBox.Show(errorDB);
                return;
            }

            SqlCeCommand cmd;
            // zapilivaem spend dannie in case of pause
            cmd = new SqlCeCommand("UPDATE Stats SET SPENDTIME=@spendtime WHERE ID=1", conn);

            cmd.Parameters.AddWithValue("@spendtime", st.spendTime.Hours*60*60 + st.spendTime.Minutes*60 + st.spendTime.Seconds);

            try
            {
                cmd.ExecuteNonQuery();
            }
            catch
            {
                System.Windows.Forms.MessageBox.Show(errorDB);
                return;
            }

            conn.Close();
            conn.Dispose();

        }

        public static void StatsComplete(Statistic st)
        {
            SqlCeConnection conn = new SqlCeConnection(connStr);
            try
            {
                conn.Open();
            }
            catch
            {
                System.Windows.Forms.MessageBox.Show(errorDB);
                return;
            }

            SqlCeCommand cmd;
           // zapilivaem spend dannie i zavershenie
            cmd = new SqlCeCommand("UPDATE Stats SET DONETASKS=@donetasks, ERROREVALUATE=@errorevaluate, ERROREVALUATEBYTASK=@errorevaluatebytask WHERE ID=1", conn);
            //cmd = new SqlCeCommand("UPDATE Stats SET DONETASKS=@donetasks ERROREVALUATE=@errorevaluate WHERE ID=1", conn);
            
            cmd.Parameters.AddWithValue("@donetasks", st.doneTasks);
            cmd.Parameters.AddWithValue("@errorevaluate", st.errorEvaluate.Hours * 60 * 60 + st.errorEvaluate.Minutes*60 + st.errorEvaluate.Seconds);
            cmd.Parameters.AddWithValue("@errorevaluatebytask", st.errorEvaluateByTask.Hours*60*60 + st.errorEvaluateByTask.Minutes*60+st.errorEvaluateByTask.Seconds);
          
            try
            {
                cmd.ExecuteNonQuery();
            }
            catch
            {
                System.Windows.Forms.MessageBox.Show(errorDB);
                return;
            }

            conn.Close();
            conn.Dispose();

        }

        public static void BaseStats(Statistic st)
        {
            SqlCeConnection conn = new SqlCeConnection(connStr);
            try
            {
                conn.Open();
            }
            catch
            {
                System.Windows.Forms.MessageBox.Show(errorDB);
                return;
            }
            SqlCeCommand cmd = new SqlCeCommand("Insert into Stats" +
               "(ID,ALLTASKS,DONETASKS,PROCESSESTASKS,EVALUATETIME,SPENDTIME,ERROREVALUATE,ERROREVALUATEBYTASK) Values (@id,@all,@done,@proc,@eval,@spend,@erroreval,@errorevalbytask)", conn);
            SqlCeParameter param = new SqlCeParameter();

            cmd.Parameters.AddWithValue("@id", 1);
            cmd.Parameters.AddWithValue("@all", st.allTasks);
            cmd.Parameters.AddWithValue("@done", st.doneTasks);
            cmd.Parameters.AddWithValue("@proc", st.processesTasks);
            cmd.Parameters.AddWithValue("@eval", 0); 
            cmd.Parameters.AddWithValue("@spend", 0); 
            cmd.Parameters.AddWithValue("@erroreval", 0);
            cmd.Parameters.AddWithValue("@errorevalbytask", 0);

            try
            {
                cmd.ExecuteNonQuery();
            }
            catch
            {
                System.Windows.Forms.MessageBox.Show(errorDB);
                return;
            }
            conn.Close();
            conn.Dispose();
            return;
        }

    }
}